																					package priv.mill.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import priv.mill.model.dept;

public class JdbcTest {

	private static Properties properties;
	static {
		properties = new Properties();
		InputStream inStream = JdbcTest.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			properties.load(inStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {

		update("insert into dept values (?,?,?)", 105, "王祖蓝", "香港");

		List<dept> query = query("select * from dept ", dept.class);

//		System.out.println(query);
	}

	/**
	 * 锟斤拷锟捷匡拷锟窖拷呒锟�
	 * 
	 * @param sql
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	public static <T> List<T> query(String sql, Class<T> clzz, Object... args) throws Exception {
		// 1. 锟斤拷锟斤拷锟斤拷锟斤拷
		Class.forName(properties.getProperty("db.driver"));
		// 2. 锟斤拷取锟斤拷锟斤拷
		// 3. 预锟斤拷锟斤拷sql
		// 4. 锟斤拷取锟斤拷锟斤拷锟�
		// 5. 锟斤拷锟斤拷锟斤拷锟斤拷
		// 6. 锟截憋拷锟斤拷源

		List<T> list = null;
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DriverManager.getConnection(properties.getProperty("db.url"),
					properties.getProperty("db.user"), properties.getProperty("db.passwd"));
					ps = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			
			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();

			T obj = null;

			list = new ArrayList<>();

			while (rs.next()) { // 每一锟轿碉拷锟斤拷锟斤拷锟斤拷一锟斤拷
				obj = clzz.getConstructor().newInstance();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					try {
						Field field = clzz.getDeclaredField(undeline2Camel(metaData.getColumnName(i + 1)));
						field.setAccessible(true);
						field.set(obj, rs.getObject(i + 1));
					} catch (NoSuchFieldException e) {// 锟斤拷锟捷匡拷锟斤拷锟斤拷锟斤拷侄危锟绞碉拷锟斤拷锟矫伙拷锟�
						e.printStackTrace();
					}
				}
				list.add(obj);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs!=null)
				rs.close();
			if(ps!=null)
				ps.close();
			if(connection!=null)
				connection.close();
		}
		return list;
	}

	/**
	 * 锟斤拷锟捷匡拷锟窖拷呒锟�
	 * 
	 * @param sql
	 * @throws Exception
	 */
	public static boolean update(String sql, Object... args) throws Exception {

		Class.forName(properties.getProperty("db.driver"));

		try (Connection connection = DriverManager.getConnection(properties.getProperty("db.url"),
				properties.getProperty("db.user"), properties.getProperty("db.passwd"));
				PreparedStatement ps = connection.prepareStatement(sql);) {

			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			int line = ps.executeUpdate(); 

			return line > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	private static String undeline2Camel(String columnName) {
		// TODO Auto-generated method stub
		return columnName;
	}
}
